{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import pymssql\n",
    "\n",
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy.types import VARCHAR, Float, Integer, Date, Numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "连接成功!\n"
     ]
    }
   ],
   "source": [
    "\n",
    "connect = pymssql.connect('192.168.3.112', 'sa', 'taotao778899!', 'DB_INFO', charset='utf8')  #建立连接\n",
    "if connect:\n",
    "    print(\"连接成功!\")\n"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": [
    "cursor = connect.cursor()   #创建一个游标对象,python里的sql语句都要通过cursor来执行\n",
    "sql = \"select name, sex from C_test\"\n",
    "cursor.execute(sql)   #执行sql语句\n",
    "row = cursor.fetchone()  #读取查询结果,\n",
    "while row:              #循环读取所有结果\n",
    "    print(\"Name=%s, Sex=%s\" % (row[0],row[1]))   #输出结果\n",
    "    row = cursor.fetchone()\n",
    "\n",
    "cursor.close()   \n",
    "connect.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TradingCode</th>\n",
       "      <th>SecuAbbr</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>00001</td>\n",
       "      <td>长和</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>00002</td>\n",
       "      <td>中电控股</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>00003</td>\n",
       "      <td>香港中华煤气</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TradingCode SecuAbbr\n",
       "0       00001       长和\n",
       "1       00002     中电控股\n",
       "2       00003   香港中华煤气"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"select top 10 TradingCode, CONVERT(nvarchar(100),SecuAbbr) SecuAbbr from HK_BASICINFO\"\n",
    "df0 = pd.read_sql(sql,connect)\n",
    "df=pd.DataFrame(df0)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TradingCode</th>\n",
       "      <th>SecuAbbr</th>\n",
       "      <th>TradingCode</th>\n",
       "      <th>SecuName</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>KBA</td>\n",
       "      <td>KraneShares博时MSCI中国A股ETF</td>\n",
       "      <td>KBA</td>\n",
       "      <td>KraneShares博时MSCI中国A股ETF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>VIMC</td>\n",
       "      <td>中星微电子</td>\n",
       "      <td>VIMC</td>\n",
       "      <td>中星微电子</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>QGEN</td>\n",
       "      <td>快而精医药</td>\n",
       "      <td>QGEN</td>\n",
       "      <td>快而精医药</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TradingCode                  SecuAbbr TradingCode                  SecuName\n",
       "0         KBA  KraneShares博时MSCI中国A股ETF         KBA  KraneShares博时MSCI中国A股ETF\n",
       "1        VIMC                     中星微电子        VIMC                     中星微电子\n",
       "2        QGEN                     快而精医药        QGEN                     快而精医药"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql = \"select top 10  TradingCode, CONVERT(nvarchar(100),SecuAbbr) SecuAbbr, TradingCode, CONVERT(nvarchar(100),SecuName) SecuName from US_StockBasicInfo\"\n",
    "df0 = pd.read_sql(sql,connect)\n",
    "df=pd.DataFrame(df0)\n",
    "df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_sql(name='DB_INFO', con=connect, if_exists='append', index=False, dtype=dtypedict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>hqtime</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>preclose</th>\n",
       "      <th>symbol</th>\n",
       "      <th>updownrate</th>\n",
       "      <th>value</th>\n",
       "      <th>vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986/4/2 0:00:00</td>\n",
       "      <td>19.2</td>\n",
       "      <td>19.3</td>\n",
       "      <td>19860402</td>\n",
       "      <td>18.9</td>\n",
       "      <td>19.3</td>\n",
       "      <td>19.3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1491300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986/4/3 0:00:00</td>\n",
       "      <td>19.1</td>\n",
       "      <td>19.3</td>\n",
       "      <td>19860403</td>\n",
       "      <td>19.0</td>\n",
       "      <td>19.2</td>\n",
       "      <td>19.2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.994792</td>\n",
       "      <td>0</td>\n",
       "      <td>1806000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986/4/4 0:00:00</td>\n",
       "      <td>19.1</td>\n",
       "      <td>19.2</td>\n",
       "      <td>19860404</td>\n",
       "      <td>18.8</td>\n",
       "      <td>19.1</td>\n",
       "      <td>19.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>3322300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986/4/7 0:00:00</td>\n",
       "      <td>19.7</td>\n",
       "      <td>19.8</td>\n",
       "      <td>19860407</td>\n",
       "      <td>19.3</td>\n",
       "      <td>19.3</td>\n",
       "      <td>19.1</td>\n",
       "      <td>1</td>\n",
       "      <td>1.031414</td>\n",
       "      <td>0</td>\n",
       "      <td>2602500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986/4/8 0:00:00</td>\n",
       "      <td>20.2</td>\n",
       "      <td>20.3</td>\n",
       "      <td>19860408</td>\n",
       "      <td>19.8</td>\n",
       "      <td>19.8</td>\n",
       "      <td>19.7</td>\n",
       "      <td>1</td>\n",
       "      <td>1.025381</td>\n",
       "      <td>0</td>\n",
       "      <td>2001200</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               time  close  high    hqtime   low  open  preclose  symbol  \\\n",
       "0  1986/4/2 0:00:00   19.2  19.3  19860402  18.9  19.3      19.3       1   \n",
       "1  1986/4/3 0:00:00   19.1  19.3  19860403  19.0  19.2      19.2       1   \n",
       "2  1986/4/4 0:00:00   19.1  19.2  19860404  18.8  19.1      19.1       1   \n",
       "3  1986/4/7 0:00:00   19.7  19.8  19860407  19.3  19.3      19.1       1   \n",
       "4  1986/4/8 0:00:00   20.2  20.3  19860408  19.8  19.8      19.7       1   \n",
       "\n",
       "   updownrate  value      vol  \n",
       "0    0.000000      0  1491300  \n",
       "1    0.994792      0  1806000  \n",
       "2    1.000000      0  3322300  \n",
       "3    1.031414      0  2602500  \n",
       "4    1.025381      0  2001200  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"F:/Test_TickDataHK.csv\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986-04-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986-04-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986-04-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986-04-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986-04-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        time\n",
       "0 1986-04-02\n",
       "1 1986-04-03\n",
       "2 1986-04-04\n",
       "3 1986-04-07\n",
       "4 1986-04-08"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tradeDate = pd.DataFrame(df.time, dtype=\"datetime64[ns]\")\n",
    "#df_tradeDate = df_tradeDate.astype(\"datetime64[ns]\")\n",
    "df_tradeDate.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>SecuAbbr</th>\n",
       "      <th>InnerCode</th>\n",
       "      <th>TradingCode</th>\n",
       "      <th>SecuAbbr</th>\n",
       "      <th>ChiSpelling</th>\n",
       "      <th>SecuCategoryCodeII</th>\n",
       "      <th>SecuCategory</th>\n",
       "      <th>ExchangeCode</th>\n",
       "      <th>ExchangeName</th>\n",
       "      <th>ListingDate</th>\n",
       "      <th>...</th>\n",
       "      <th>CycleRatio</th>\n",
       "      <th>SHHKStockConnect</th>\n",
       "      <th>LastModifyTime</th>\n",
       "      <th>SZHKStockConnect</th>\n",
       "      <th>employee</th>\n",
       "      <th>mainbusiness</th>\n",
       "      <th>FlowShares</th>\n",
       "      <th>IsManual</th>\n",
       "      <th>FiscalDate</th>\n",
       "      <th>Spread</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>长和</td>\n",
       "      <td>3532</td>\n",
       "      <td>00001</td>\n",
       "      <td>³¤ºÍ</td>\n",
       "      <td>CH</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1972-11-01</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-18 17:00:03.530</td>\n",
       "      <td>1</td>\n",
       "      <td>300000</td>\n",
       "      <td></td>\n",
       "      <td>3.840422e+09</td>\n",
       "      <td>None</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>中电控股</td>\n",
       "      <td>3533</td>\n",
       "      <td>00002</td>\n",
       "      <td>ÖÐµç¿Ø¹É</td>\n",
       "      <td>ZDKG</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1980-01-02</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-18 17:00:26.823</td>\n",
       "      <td>1</td>\n",
       "      <td>8060</td>\n",
       "      <td></td>\n",
       "      <td>2.526451e+09</td>\n",
       "      <td>None</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>香港中华煤气</td>\n",
       "      <td>3534</td>\n",
       "      <td>00003</td>\n",
       "      <td>Ïã¸ÛÖÐ»ªÃºÆø</td>\n",
       "      <td>XGZHMQ</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1960-04-11</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-18 17:00:06.690</td>\n",
       "      <td>1</td>\n",
       "      <td>2130</td>\n",
       "      <td></td>\n",
       "      <td>1.865987e+10</td>\n",
       "      <td>None</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>九龙仓集团</td>\n",
       "      <td>3535</td>\n",
       "      <td>00004</td>\n",
       "      <td>¾ÅÁú²Ö¼¯ÍÅ</td>\n",
       "      <td>JLCJT</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1921-01-01</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-18 17:00:12.897</td>\n",
       "      <td>1</td>\n",
       "      <td>7100</td>\n",
       "      <td></td>\n",
       "      <td>3.056027e+09</td>\n",
       "      <td>None</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>汇丰控股</td>\n",
       "      <td>3536</td>\n",
       "      <td>00005</td>\n",
       "      <td>»ã·á¿Ø¹É</td>\n",
       "      <td>HFKG</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1991-01-02</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1</td>\n",
       "      <td>2021-09-18 17:00:18.573</td>\n",
       "      <td>1</td>\n",
       "      <td>226000</td>\n",
       "      <td></td>\n",
       "      <td>2.075161e+10</td>\n",
       "      <td>None</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "  SecuAbbr  InnerCode TradingCode      SecuAbbr ChiSpelling  \\\n",
       "0       长和       3532       00001          ³¤ºÍ          CH   \n",
       "1     中电控股       3533       00002      ÖÐµç¿Ø¹É        ZDKG   \n",
       "2   香港中华煤气       3534       00003  Ïã¸ÛÖÐ»ªÃºÆø      XGZHMQ   \n",
       "3    九龙仓集团       3535       00004    ¾ÅÁú²Ö¼¯ÍÅ       JLCJT   \n",
       "4     汇丰控股       3536       00005      »ã·á¿Ø¹É        HFKG   \n",
       "\n",
       "   SecuCategoryCodeII SecuCategory  ExchangeCode    ExchangeName ListingDate  \\\n",
       "0                1010          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1972-11-01   \n",
       "1                1010          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1980-01-02   \n",
       "2                1010          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1960-04-11   \n",
       "3                1010          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1921-01-01   \n",
       "4                1010          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1991-01-02   \n",
       "\n",
       "   ...  CycleRatio SHHKStockConnect          LastModifyTime SZHKStockConnect  \\\n",
       "0  ...        None                1 2021-09-18 17:00:03.530                1   \n",
       "1  ...        None                1 2021-09-18 17:00:26.823                1   \n",
       "2  ...        None                1 2021-09-18 17:00:06.690                1   \n",
       "3  ...        None                1 2021-09-18 17:00:12.897                1   \n",
       "4  ...        None                1 2021-09-18 17:00:18.573                1   \n",
       "\n",
       "   employee mainbusiness    FlowShares IsManual FiscalDate Spread  \n",
       "0    300000               3.840422e+09     None      12/31     01  \n",
       "1      8060               2.526451e+09     None      12/31     01  \n",
       "2      2130               1.865987e+10     None      12/31     01  \n",
       "3      7100               3.056027e+09     None      12/31     01  \n",
       "4    226000               2.075161e+10     None      12/31     01  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df0.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TradingDate</th>\n",
       "      <th>Market</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986-04-02</td>\n",
       "      <td>HK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986-04-03</td>\n",
       "      <td>HK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986-04-04</td>\n",
       "      <td>HK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986-04-07</td>\n",
       "      <td>HK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986-04-08</td>\n",
       "      <td>HK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TradingDate Market\n",
       "0  1986-04-02     HK\n",
       "1  1986-04-03     HK\n",
       "2  1986-04-04     HK\n",
       "3  1986-04-07     HK\n",
       "4  1986-04-08     HK"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_tradeDate['Market'] = 'HK'\n",
    "df_tradeDate['Market'] = df_tradeDate['Market'].astype('str')\n",
    "df_tradeDate.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TradingDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986-04-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986-04-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986-04-04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986-04-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986-04-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  TradingDate\n",
       "0  1986-04-02\n",
       "1  1986-04-03\n",
       "2  1986-04-04\n",
       "3  1986-04-07\n",
       "4  1986-04-08"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#df_tradeDate.time = pd.to_datetime(df_tradeDate.time)\n",
    "df_tradeDate = df_tradeDate.rename(columns={'time':\"TradingDate\"})\n",
    "df_tradeDate.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>InnerCode</th>\n",
       "      <th>TradingCode</th>\n",
       "      <th>SecuAbbr</th>\n",
       "      <th>ChiSpelling</th>\n",
       "      <th>SecuCategoryCodeII</th>\n",
       "      <th>SecuCategory</th>\n",
       "      <th>ExchangeCode</th>\n",
       "      <th>ExchangeName</th>\n",
       "      <th>ListingDate</th>\n",
       "      <th>DistPrice</th>\n",
       "      <th>...</th>\n",
       "      <th>CycleRatio</th>\n",
       "      <th>SHHKStockConnect</th>\n",
       "      <th>LastModifyTime</th>\n",
       "      <th>SZHKStockConnect</th>\n",
       "      <th>employee</th>\n",
       "      <th>mainbusiness</th>\n",
       "      <th>FlowShares</th>\n",
       "      <th>IsManual</th>\n",
       "      <th>FiscalDate</th>\n",
       "      <th>Spread</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3532</td>\n",
       "      <td>00001</td>\n",
       "      <td>³¤ºÍ</td>\n",
       "      <td>CH</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1972-11-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-09-18 16:54:41.637</td>\n",
       "      <td>1.0</td>\n",
       "      <td>300000</td>\n",
       "      <td></td>\n",
       "      <td>3.840422e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3533</td>\n",
       "      <td>00002</td>\n",
       "      <td>ÖÐµç¿Ø¹É</td>\n",
       "      <td>ZDKG</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1980-01-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-09-18 16:54:38.570</td>\n",
       "      <td>1.0</td>\n",
       "      <td>8060</td>\n",
       "      <td></td>\n",
       "      <td>2.526451e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3534</td>\n",
       "      <td>00003</td>\n",
       "      <td>Ïã¸ÛÖÐ»ªÃºÆø</td>\n",
       "      <td>XGZHMQ</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1960-04-11</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-09-18 16:54:37.230</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2130</td>\n",
       "      <td></td>\n",
       "      <td>1.865987e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3535</td>\n",
       "      <td>00004</td>\n",
       "      <td>¾ÅÁú²Ö¼¯ÍÅ</td>\n",
       "      <td>JLCJT</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1921-01-01</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-09-18 16:54:36.633</td>\n",
       "      <td>1.0</td>\n",
       "      <td>7100</td>\n",
       "      <td></td>\n",
       "      <td>3.056027e+09</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3536</td>\n",
       "      <td>00005</td>\n",
       "      <td>»ã·á¿Ø¹É</td>\n",
       "      <td>HFKG</td>\n",
       "      <td>1010</td>\n",
       "      <td>H¹É</td>\n",
       "      <td>161</td>\n",
       "      <td>Ïã¸ÛÁªºÏ½»Ò×Ëù</td>\n",
       "      <td>1991-01-02</td>\n",
       "      <td>0.0</td>\n",
       "      <td>...</td>\n",
       "      <td>None</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2021-09-18 16:54:42.153</td>\n",
       "      <td>1.0</td>\n",
       "      <td>226000</td>\n",
       "      <td></td>\n",
       "      <td>2.075161e+10</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12/31</td>\n",
       "      <td>01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   InnerCode TradingCode      SecuAbbr ChiSpelling  SecuCategoryCodeII  \\\n",
       "0       3532       00001          ³¤ºÍ          CH                1010   \n",
       "1       3533       00002      ÖÐµç¿Ø¹É        ZDKG                1010   \n",
       "2       3534       00003  Ïã¸ÛÖÐ»ªÃºÆø      XGZHMQ                1010   \n",
       "3       3535       00004    ¾ÅÁú²Ö¼¯ÍÅ       JLCJT                1010   \n",
       "4       3536       00005      »ã·á¿Ø¹É        HFKG                1010   \n",
       "\n",
       "  SecuCategory  ExchangeCode    ExchangeName ListingDate  DistPrice  ...  \\\n",
       "0          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1972-11-01        0.0  ...   \n",
       "1          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1980-01-02        0.0  ...   \n",
       "2          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1960-04-11        0.0  ...   \n",
       "3          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1921-01-01        0.0  ...   \n",
       "4          H¹É           161  Ïã¸ÛÁªºÏ½»Ò×Ëù  1991-01-02        0.0  ...   \n",
       "\n",
       "  CycleRatio  SHHKStockConnect          LastModifyTime  SZHKStockConnect  \\\n",
       "0       None               1.0 2021-09-18 16:54:41.637               1.0   \n",
       "1       None               1.0 2021-09-18 16:54:38.570               1.0   \n",
       "2       None               1.0 2021-09-18 16:54:37.230               1.0   \n",
       "3       None               1.0 2021-09-18 16:54:36.633               1.0   \n",
       "4       None               1.0 2021-09-18 16:54:42.153               1.0   \n",
       "\n",
       "  employee mainbusiness    FlowShares IsManual FiscalDate Spread  \n",
       "0   300000               3.840422e+09      NaN      12/31     01  \n",
       "1     8060               2.526451e+09      NaN      12/31     01  \n",
       "2     2130               1.865987e+10      NaN      12/31     01  \n",
       "3     7100               3.056027e+09      NaN      12/31     01  \n",
       "4   226000               2.075161e+10      NaN      12/31     01  \n",
       "\n",
       "[5 rows x 50 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# conn = create_engine('mssql+pymssql://sa:taotao778899!@192.168.3.112:3306/DB_INFO?charset=utf8')  \n",
    "# conn = create_engine('mysql+pymysql://root:password@localhost:3306/databasename?charset=utf8')  \n",
    "conn = create_engine('mssql+pymssql://%s:%s@%s/%s' % (\n",
    "                             \"sa\",\n",
    "                             \"taotao778899!\",\n",
    "                             \"192.168.3.112\",\n",
    "                             # 3306,\n",
    "                             \"DB_INFO\"                               \n",
    "                             ),connect_args={'charset':'utf8'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "dtypedict = {\n",
    "  'Market': VARCHAR(length=3),\n",
    "  'time': Date\n",
    "}\n",
    "df_tradeDate.to_sql(name='TradingDate', con=conn, if_exists='append', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>time</th>\n",
       "      <th>close</th>\n",
       "      <th>high</th>\n",
       "      <th>hqtime</th>\n",
       "      <th>low</th>\n",
       "      <th>open</th>\n",
       "      <th>preclose</th>\n",
       "      <th>symbol</th>\n",
       "      <th>updownrate</th>\n",
       "      <th>value</th>\n",
       "      <th>vol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1986/4/2 0:00:00</td>\n",
       "      <td>19.20</td>\n",
       "      <td>19.30</td>\n",
       "      <td>19860402</td>\n",
       "      <td>18.90</td>\n",
       "      <td>19.30</td>\n",
       "      <td>19.30</td>\n",
       "      <td>1</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>1491300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1986/4/3 0:00:00</td>\n",
       "      <td>19.10</td>\n",
       "      <td>19.30</td>\n",
       "      <td>19860403</td>\n",
       "      <td>19.00</td>\n",
       "      <td>19.20</td>\n",
       "      <td>19.20</td>\n",
       "      <td>1</td>\n",
       "      <td>0.994792</td>\n",
       "      <td>0</td>\n",
       "      <td>1806000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1986/4/4 0:00:00</td>\n",
       "      <td>19.10</td>\n",
       "      <td>19.20</td>\n",
       "      <td>19860404</td>\n",
       "      <td>18.80</td>\n",
       "      <td>19.10</td>\n",
       "      <td>19.10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>3322300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1986/4/7 0:00:00</td>\n",
       "      <td>19.70</td>\n",
       "      <td>19.80</td>\n",
       "      <td>19860407</td>\n",
       "      <td>19.30</td>\n",
       "      <td>19.30</td>\n",
       "      <td>19.10</td>\n",
       "      <td>1</td>\n",
       "      <td>1.031414</td>\n",
       "      <td>0</td>\n",
       "      <td>2602500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1986/4/8 0:00:00</td>\n",
       "      <td>20.20</td>\n",
       "      <td>20.30</td>\n",
       "      <td>19860408</td>\n",
       "      <td>19.80</td>\n",
       "      <td>19.80</td>\n",
       "      <td>19.70</td>\n",
       "      <td>1</td>\n",
       "      <td>1.025381</td>\n",
       "      <td>0</td>\n",
       "      <td>2001200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8735</th>\n",
       "      <td>2021/7/20 0:00:00</td>\n",
       "      <td>57.15</td>\n",
       "      <td>57.85</td>\n",
       "      <td>20210720</td>\n",
       "      <td>56.95</td>\n",
       "      <td>57.20</td>\n",
       "      <td>57.90</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>270140647</td>\n",
       "      <td>4718150</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8736</th>\n",
       "      <td>2021/7/21 0:00:00</td>\n",
       "      <td>56.90</td>\n",
       "      <td>57.60</td>\n",
       "      <td>20210721</td>\n",
       "      <td>56.70</td>\n",
       "      <td>57.35</td>\n",
       "      <td>57.15</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>166912775</td>\n",
       "      <td>2926746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8737</th>\n",
       "      <td>2021/7/22 0:00:00</td>\n",
       "      <td>57.80</td>\n",
       "      <td>58.20</td>\n",
       "      <td>20210722</td>\n",
       "      <td>56.80</td>\n",
       "      <td>56.80</td>\n",
       "      <td>56.90</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>191430223</td>\n",
       "      <td>3315843</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8738</th>\n",
       "      <td>2021/7/23 0:00:00</td>\n",
       "      <td>57.35</td>\n",
       "      <td>57.65</td>\n",
       "      <td>20210723</td>\n",
       "      <td>57.20</td>\n",
       "      <td>57.50</td>\n",
       "      <td>57.80</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>134666664</td>\n",
       "      <td>2346165</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8739</th>\n",
       "      <td>2021/7/26 0:00:00</td>\n",
       "      <td>57.35</td>\n",
       "      <td>58.20</td>\n",
       "      <td>20210726</td>\n",
       "      <td>56.90</td>\n",
       "      <td>57.35</td>\n",
       "      <td>57.35</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>225997467</td>\n",
       "      <td>3923370</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8740 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   time  close   high    hqtime    low   open  preclose  \\\n",
       "0      1986/4/2 0:00:00  19.20  19.30  19860402  18.90  19.30     19.30   \n",
       "1      1986/4/3 0:00:00  19.10  19.30  19860403  19.00  19.20     19.20   \n",
       "2      1986/4/4 0:00:00  19.10  19.20  19860404  18.80  19.10     19.10   \n",
       "3      1986/4/7 0:00:00  19.70  19.80  19860407  19.30  19.30     19.10   \n",
       "4      1986/4/8 0:00:00  20.20  20.30  19860408  19.80  19.80     19.70   \n",
       "...                 ...    ...    ...       ...    ...    ...       ...   \n",
       "8735  2021/7/20 0:00:00  57.15  57.85  20210720  56.95  57.20     57.90   \n",
       "8736  2021/7/21 0:00:00  56.90  57.60  20210721  56.70  57.35     57.15   \n",
       "8737  2021/7/22 0:00:00  57.80  58.20  20210722  56.80  56.80     56.90   \n",
       "8738  2021/7/23 0:00:00  57.35  57.65  20210723  57.20  57.50     57.80   \n",
       "8739  2021/7/26 0:00:00  57.35  58.20  20210726  56.90  57.35     57.35   \n",
       "\n",
       "      symbol  updownrate      value      vol  \n",
       "0          1    0.000000          0  1491300  \n",
       "1          1    0.994792          0  1806000  \n",
       "2          1    1.000000          0  3322300  \n",
       "3          1    1.031414          0  2602500  \n",
       "4          1    1.025381          0  2001200  \n",
       "...      ...         ...        ...      ...  \n",
       "8735       1         NaN  270140647  4718150  \n",
       "8736       1         NaN  166912775  2926746  \n",
       "8737       1         NaN  191430223  3315843  \n",
       "8738       1         NaN  134666664  2346165  \n",
       "8739       1         NaN  225997467  3923370  \n",
       "\n",
       "[8740 rows x 11 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def mapping_df_types(df):\n",
    "    dtypedict = {}\n",
    "    for i, j in zip(df.columns, df.dtypes):\n",
    "        if \"object\" in str(j):\n",
    "            dtypedict.update({i: NVARCHAR(length=255)})\n",
    "        if \"float\" in str(j):\n",
    "            dtypedict.update({i: Float(precision=2, asdecimal=True)})\n",
    "        if \"int\" in str(j):\n",
    "            dtypedict.update({i: Integer()})\n",
    "    return dtypedict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0 00001\n",
      "1 00002\n",
      "2 00003\n",
      "3 00004\n",
      "4 00005\n",
      "5 00006\n",
      "6 00007\n",
      "7 00008\n",
      "8 00009\n",
      "9 00010\n"
     ]
    }
   ],
   "source": [
    "for index, row in df.iterrows():\n",
    "    print(index, row['TradingCode'])"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
